import pandas as pd
import pandahouse as ph
import numpy as np
import pandas_profiling
import plotly.express as px
import datetime
Посчитать OnTime - Метрика своевременности доставки. (Доставили в плановую дату доставки или нет), в %
Необходимо видеть изменение метрики по дням;
Необходимы разрезы по перевозчикам, по типу доставки, по кластерам склада, по таймслотам;
connection_default = {'host': 'http://clickhouse.beslan.pro:8080',
'database':'default',
'user':'******',
'password':'******'
}
'''К сожалению, данные в базе выглядят таким образом, что просто считать их не удалось. У столбцов с
датами необычный формат, даты имеют вид типа '2021-16-01' или '2021-8-01', множество пустых ячеек
и тип Nullable(String). В связи с чем пришлось считывать их как строки, используя функцию assumeNotNull.'''
q = '''
SELECT CAST(assumeNotNull(date_order) as String) as date_order,
number_order, CAST(planned_data_order as String) as planned_data_order,
timeslot_number, CAST(assumeNotNull(result_data_order) as String) as result_data_order,
delivery_type, carrier_name, seller_ID, order_status, customer_cluster,
warehouse_cluster
FROM
{db}.logistics_online
'''
logistics_online = ph.read_clickhouse(query=q, connection=connection_default)
logistics_online.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-5-01 | 313655114 | 2021-14-01 | 1 | 2021-11-01 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Краснодар | Кластер Москва |
| 1 | 2021-3-01 | 315229153 | 2021-11-01 | 1 | 2021-8-01 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 2 | 2021-8-01 | 3114106095 | 2021-16-01 | 1 | 2021-14-01 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 3 | 2021-12-01 | 31145541407 | 2021-20-01 | 1 | 2021-15-01 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Санкт-Петербург | Кластер Москва |
| 4 | 2021-16-01 | 325050136 | 2021-26-01 | 1 | 2021-25-01 | Самовывоз | 3PL 1 | 946233 | Доставлен | Кластер Санкт-Петербург | Кластер Санкт-Петербург |
'''Теперь заменим все пустые ячейки на NaN и преобразуем столбцы с датами к нужному формату.'''
logistics_online = logistics_online.replace('', np.nan)
logistics_online['date_order'] = pd.to_datetime(logistics_online['date_order'],
errors='coerce', format='%Y-%d-%m')
logistics_online['result_data_order'] = pd.to_datetime(logistics_online['result_data_order'],
errors='coerce', format='%Y-%d-%m')
logistics_online['planned_data_order'] = pd.to_datetime(logistics_online['planned_data_order'],
errors='coerce', format='%Y-%d-%m')
logistics_online.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-05 | 313655114 | 2021-01-14 | 1 | 2021-01-11 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Краснодар | Кластер Москва |
| 1 | 2021-01-03 | 315229153 | 2021-01-11 | 1 | 2021-01-08 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 2 | 2021-01-08 | 3114106095 | 2021-01-16 | 1 | 2021-01-14 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 3 | 2021-01-12 | 31145541407 | 2021-01-20 | 1 | 2021-01-15 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Санкт-Петербург | Кластер Москва |
| 4 | 2021-01-16 | 325050136 | 2021-01-26 | 1 | 2021-01-25 | Самовывоз | 3PL 1 | 946233 | Доставлен | Кластер Санкт-Петербург | Кластер Санкт-Петербург |
pandas_profiling.ProfileReport(logistics_online)
Summarize dataset: 0%| | 0/25 [00:00<?, ?it/s]
Generate report structure: 0%| | 0/1 [00:00<?, ?it/s]
Render HTML: 0%| | 0/1 [00:00<?, ?it/s]
'''Да, действительно один и тот же заказ встречается трижды. К строке со вторым таймслотом вопросов
нет, заказ не смогли доставить в плановую дату с первой попытки и перенесли. А вот оставшиеся две строки
похожи на баг, они абсолютно идентичны. Удалим одну из них'''
logistics_online[logistics_online['number_order']==344629456]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 726 | 2021-02-16 | 344629456 | 2021-02-26 | 1 | 2021-03-03 | Курьер | 3PL 1 | 9504 | Доставлен | Кластер Калининград | Кластер Казань |
| 727 | NaT | 344629456 | 2021-03-03 | 2 | 2021-03-03 | Курьер | 3PL 1 | 9504 | Доставлен | Кластер Калининград | Кластер Казань |
| 728 | 2021-02-16 | 344629456 | 2021-02-26 | 1 | 2021-03-03 | Курьер | 3PL 1 | 9504 | Доставлен | Кластер Калининград | Кластер Казань |
logistics_online = logistics_online.drop_duplicates(keep = 'first')
'''Очень много NaN значений. Вероятно, причина в некорректном сборе данных, при наличии возможности
нужно было бы разобраться, но у нас ее нет. Просто удалить все NaN нельзя, это почти 15% всех имеющихся данных.
Попробуем разобраться, что не так, исходя из имеющейся информации'''
logistics_online.isna().sum()
date_order 658 number_order 0 planned_data_order 0 timeslot_number 0 result_data_order 934 delivery_type 0 carrier_name 0 seller_ID 0 order_status 0 customer_cluster 0 warehouse_cluster 0 dtype: int64
'''Если просто посмотреть на NaN значения в столбце с датой заказа, видно, что они, как правило стоят у заказов
с таймслотами больше одного. Вероятно, когда заказ не успевают доставить и он переносится, дата заказа
по какой-то причине в системе повторно не проставляется. Во-первых, в контексте данного задания дата заказа
нас вообще не интересует. Во-вторых, это не багованные заказы, они доставлены и у них есть дата заказа там, где
заказ с этим же номером проходит с таймслотом номер 1. Поэтому мы вообще не будем их трогать. Все равно при
дальнейших расчетах нас будут интересовать только уникальные номера заказов, дубли номеров мы удалим и часть
этих заказов удалится.'''
logistics_online[logistics_online['date_order'].isna()].head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 11 | NaT | 3366721496 | 2021-02-16 | 2 | 2021-02-15 | Самовывоз | 3PL 3 | 94320 | Доставлен | Кластер Екатеринбург | Кластер Московская область |
| 15 | NaT | 337626360 | 2021-02-19 | 2 | 2021-02-16 | Самовывоз | 3PL 3 | 5038 | Доставлен | Кластер Санкт-Петербург | Кластер Центр |
| 20 | NaT | 342545229 | 2021-02-19 | 2 | 2021-02-19 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Москва | Кластер Московская область |
| 21 | NaT | 346099025 | 2021-03-15 | 2 | 2021-03-05 | Курьер | 3PL 1 | 629123 | Доставлен | Кластер Московская область | Кластер Санкт-Петербург |
| 23 | NaT | 34726147014 | 2021-03-03 | 2 | 2021-03-02 | Курьер | 3PL 1 | 202312 | Доставлен | Кластер Краснодар | Кластер Краснодар |
'''Вот с датой доставки сложнее. Это как раз то, что нас интересует в данном задании. Посмотрим на статусы.
Заказы с отсутствующей датой доставки и статусами 'Отменен', 'На пути к клиенту', 'Ожидает сборки' можно
удалять, потому что нам нужны только доставленные. На статус 'Доставлен' нужно посмотреть.'''
logistics_online[logistics_online['result_data_order'].isna()].order_status.unique()
array(['Отменен', 'На пути к клиенту', 'Ожидает сборки', 'Доставлен'],
dtype=object)
logistics_online[logistics_online['result_data_order'].isna()].query('order_status == "Доставлен"').head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 204 | NaT | 301939627 | 2021-01-03 | 1 | NaT | Самовывоз | 3PL 2 | 68503 | Доставлен | Кластер Екатеринбург | Кластер Москва |
| 462 | 2021-01-03 | 311361912 | 2021-01-12 | 1 | NaT | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва |
| 874 | 2021-01-07 | 3101420316 | 2021-01-11 | 1 | NaT | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва |
| 1007 | 2021-01-03 | 3069631404 | 2021-01-14 | 1 | NaT | Самовывоз | 3PL 2 | 68503 | Доставлен | Кластер Калининград | Кластер Москва |
| 1100 | NaT | 305674342 | 2021-01-07 | 1 | NaT | Курьер | Самодоставка | 833469 | Доставлен | Кластер Екатеринбург | Кластер Екатеринбург |
'''Возможно, просто баг в сборе данных. Но таких заказов всего 44 штуки, это менее процента от всего датафрейма.
Существенного влияния на результаты их удаление не окажет.'''
logistics_online[logistics_online['result_data_order'].isna()].query('order_status == "Доставлен"').shape
(44, 11)
logistics_without_nan = logistics_online[logistics_online['result_data_order'].notna()]
'''В итоге заказы с явно не интересующими нас статусами удалились. Статусы 'Доставлен', 'Отменен', 'Спор'
вполне могут быть у реально доставленных получателю заказов (например, в случае, если покупатель вернул заказ
уже после доставки или у товара есть какой-то брак и покупатель заказ получил, но открыл спор), поэтому
считаем данные готовыми к анализу'''
logistics_without_nan['order_status'].unique()
array(['Доставлен', 'Отменен', 'Спор'], dtype=object)
'''Для облегчения расчетов создадим дополнительный столбец, в котором напротив каждого заказа
проставим 1, если он доставлен вовремя и 0 в противном случае. Тогда для того, чтобы рассчитать процент
вовремя доставленных заказов, нужно будет просто сгруппировать дф по нужным признакам и посчитать среднее
по этому столбцу.'''
logistics_without_nan['ontime'] = np.where(logistics_without_nan['result_data_order'] <=
logistics_without_nan['planned_data_order'],
1, 0)
C:\Users\79307\anaconda3\envs\python_3_7\lib\site-packages\ipykernel_launcher.py:8: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
'''Отсортируем дф так, чтобы заказы шли по возрастанию итоговой даты доставки и по возрастанию таймслота,
затем удалим дубли номеров заказов, отавив только последний. Объясню логику. Подобная метрика действительно
существует у компании, я посмотрела это в руководстве для продавцов. Она считается сложнее, чем указано
в задании, но логика такая: берутся все заказы, доставленные в определенный день и считается процент доставленных
в пределах плановой даты от общего числа доставленных. Предположим, что один и тот же заказ
переносился дважды. Тогда он будет фигурировать в дф три раза. Плановая дата доставки будет меняться, как
и таймслот. Сама итоговая дата доставки у всех трех будет одинаковая. Во-первых, оставить все три я не могу.
Поскольку получится, что в одну дату доставки у нас два доставленных не вовремя и один доставленный
вовремя заказ. Это исказит результаты, поскольку заказ это на самом деле один и тот же. В задании не указано,
что подразумевается под 'доставленным вовремя' заказом. Вовремя с какой попытки? Плановая дата постоянно
сдвигается. Уточнить возможности нет. Поэтому я решаю оставить только последнюю попытку доставки,
и сравнивать реальную дату доставки с плановой датой, которая была установлена последней'''
logistics_without_nan = logistics_without_nan.sort_values(['result_data_order', 'timeslot_number'])
logistics_online_unique = logistics_without_nan.drop_duplicates(subset = 'number_order', keep = 'last')
'''Получаем итоговый датафрейм. Из-за обилия информации выглядит не слишко наглядно, используем
возможности визуализации, чтобы посмотреть динамику. Непонятно в задании, подразумевается, что
разрезы "внутри друг друга" или же динамика в каждом разрезе отдельно. Оставим такой датафрейм и
отдельно сделаем в каждом разрезе'''
ontime = logistics_online_unique.groupby(['result_data_order', 'carrier_name',
'delivery_type', 'warehouse_cluster', 'timeslot_number']) \
.agg({'ontime': 'mean'}).reset_index()
ontime['ontime'] = (ontime['ontime']*100)
ontime[ontime['result_data_order']=='2021-01-04']
| result_data_order | carrier_name | delivery_type | warehouse_cluster | timeslot_number | ontime | |
|---|---|---|---|---|---|---|
| 3 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 1 | 100.0 |
| 4 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 2 | 100.0 |
| 5 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Москва | 1 | 0.0 |
| 6 | 2021-01-04 | Самодоставка | Курьер | Кластер Московская область | 1 | 100.0 |
'''Просто динамика по дням. Поскольку речь идет о процентах (метрика - отношение), а признаков очень много,
мы не можем строить графики по имеющемуся дф, данные будут некорректными. Для визуализации отдельно по каждому
разрезу придется создавать свой датафрейм'''
ontime_date = logistics_online_unique.groupby('result_data_order') \
.agg({'ontime': 'mean'}).reset_index()
ontime_date['ontime'] = (ontime_date['ontime']*100)
ontime_date.head()
| result_data_order | ontime | |
|---|---|---|
| 0 | 2021-01-02 | 100.000000 |
| 1 | 2021-01-03 | 100.000000 |
| 2 | 2021-01-04 | 92.857143 |
| 3 | 2021-01-05 | 90.000000 |
| 4 | 2021-01-06 | 100.000000 |
fig = px.line(ontime_date, x="result_data_order", y="ontime",
labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()
'''Динамика по дням в разрезе перевозчиков'''
ontime_carrier = logistics_online_unique.groupby(['result_data_order', 'carrier_name']) \
.agg({'ontime': 'mean'}).reset_index()
ontime_carrier['ontime'] = (ontime_carrier['ontime']*100)
ontime_carrier.head()
| result_data_order | carrier_name | ontime | |
|---|---|---|---|
| 0 | 2021-01-02 | Самодоставка | 100.0 |
| 1 | 2021-01-03 | 3PL 2 | 100.0 |
| 2 | 2021-01-03 | Самодоставка | 100.0 |
| 3 | 2021-01-04 | 3PL 2 | 90.0 |
| 4 | 2021-01-04 | Самодоставка | 100.0 |
fig = px.line(ontime_carrier, x="result_data_order", y="ontime", color = 'carrier_name',
labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()
'''Динамика по дням в разрезе типа доставки'''
ontime_type = logistics_online_unique.groupby(['result_data_order', 'delivery_type']) \
.agg({'ontime': 'mean'}).reset_index()
ontime_type['ontime'] = (ontime_type['ontime']*100)
ontime_type.head()
| result_data_order | delivery_type | ontime | |
|---|---|---|---|
| 0 | 2021-01-02 | Курьер | 100.0 |
| 1 | 2021-01-03 | Курьер | 100.0 |
| 2 | 2021-01-03 | Самовывоз | 100.0 |
| 3 | 2021-01-04 | Курьер | 100.0 |
| 4 | 2021-01-04 | Самовывоз | 90.0 |
fig = px.line(ontime_type, x="result_data_order", y="ontime", color = 'delivery_type',
labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()
'''Динамика по дням в разрезе кластеров склада'''
online_cluster = logistics_online_unique.groupby(['result_data_order', 'warehouse_cluster']) \
.agg({'ontime': 'mean'}).reset_index()
online_cluster['ontime'] = (online_cluster['ontime']*100)
online_cluster.head()
| result_data_order | warehouse_cluster | ontime | |
|---|---|---|---|
| 0 | 2021-01-02 | Кластер Москва | 100.0 |
| 1 | 2021-01-03 | Кластер Екатеринбург | 100.0 |
| 2 | 2021-01-03 | Кластер Московская область | 100.0 |
| 3 | 2021-01-04 | Кластер Екатеринбург | 100.0 |
| 4 | 2021-01-04 | Кластер Москва | 0.0 |
fig = px.line(online_cluster, x="result_data_order", y="ontime", color = 'warehouse_cluster',
labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()
'''Динамика по дням в разрезе таймслотов'''
online_timeslot = logistics_online_unique.groupby(['result_data_order', 'timeslot_number']) \
.agg({'ontime': 'mean'}).reset_index()
online_timeslot['ontime'] = (online_timeslot['ontime']*100)
online_timeslot.head()
| result_data_order | timeslot_number | ontime | |
|---|---|---|---|
| 0 | 2021-01-02 | 1 | 100.000000 |
| 1 | 2021-01-03 | 1 | 100.000000 |
| 2 | 2021-01-04 | 1 | 92.307692 |
| 3 | 2021-01-04 | 2 | 100.000000 |
| 4 | 2021-01-05 | 1 | 87.500000 |
fig = px.line(online_timeslot, x="result_data_order", y="ontime", color = 'timeslot_number',
labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()
Посчитать PromisedClick2Delivery - метрика обещанного срока доставки (от даты заказа до первой плановой даты доставки), в сутках.
Необходима динамика по дням, неделям, месяцам;
Необходимо сравнение перевозчиков;
Необходимо понимание уровня метрики из кластера в кластер.
'''Возьмём датафрейм из первого задания. В нем уже удален один дубликат. Поскольку нас интересует
только первая плановая дата доставки, отфильтруем его по таймслоту номер 1.'''
first_delivery = logistics_online.query('timeslot_number == 1')
first_delivery.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-05 | 313655114 | 2021-01-14 | 1 | 2021-01-11 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Краснодар | Кластер Москва |
| 1 | 2021-01-03 | 315229153 | 2021-01-11 | 1 | 2021-01-08 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 2 | 2021-01-08 | 3114106095 | 2021-01-16 | 1 | 2021-01-14 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 3 | 2021-01-12 | 31145541407 | 2021-01-20 | 1 | 2021-01-15 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Санкт-Петербург | Кластер Москва |
| 4 | 2021-01-16 | 325050136 | 2021-01-26 | 1 | 2021-01-25 | Самовывоз | 3PL 1 | 946233 | Доставлен | Кластер Санкт-Петербург | Кластер Санкт-Петербург |
'''Поскольку в дф остались только заказы с таймслотом номер 1, все номера заказов должны быть уникальными.
Но здесь видно, что осталось три дубликата. Посмотрим подробнее на каждый.'''
first_delivery[first_delivery.number_order.duplicated()]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 13 | 2021-02-11 | 336693909 | 2021-02-16 | 1 | 2021-02-17 | Самовывоз | 3PL 3 | 2345336 | Доставлен | Кластер Екатеринбург | Кластер Московская область |
| 450 | 2021-03-05 | 357469624 | 2021-03-12 | 1 | NaT | Курьер | 3PL 3 | 8612129 | Отменен | Кластер Краснодар | Кластер Москва |
| 6427 | 2021-02-06 | 333776216 | 2021-02-11 | 1 | 2021-02-13 | Самовывоз | 3PL 1 | 633129 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург |
first_delivery[first_delivery['number_order']==333776216]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 6426 | 2021-02-05 | 333776216 | 2021-02-11 | 1 | 2021-02-13 | Самовывоз | 3PL 1 | 633129 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург |
| 6427 | 2021-02-06 | 333776216 | 2021-02-11 | 1 | 2021-02-13 | Самовывоз | 3PL 1 | 633129 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург |
first_delivery[first_delivery['number_order']==336693909]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 12 | 2021-02-09 | 336693909 | 2021-02-16 | 1 | 2021-02-17 | Самовывоз | 3PL 3 | 2345336 | Доставлен | Кластер Екатеринбург | Кластер Московская область |
| 13 | 2021-02-11 | 336693909 | 2021-02-16 | 1 | 2021-02-17 | Самовывоз | 3PL 3 | 2345336 | Доставлен | Кластер Екатеринбург | Кластер Московская область |
'''Во всех трёх одинаковая ситуация. Они доставлены с первой попытки, абсолютно идентичны, отличается только
дата заказа на 1-2 дня. Возможно, заказы просто задвоились в системе.'''
first_delivery[first_delivery['number_order']==357469624]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 449 | 2021-03-04 | 357469624 | 2021-03-12 | 1 | NaT | Курьер | 3PL 3 | 8612129 | Отменен | Кластер Краснодар | Кластер Москва |
| 450 | 2021-03-05 | 357469624 | 2021-03-12 | 1 | NaT | Курьер | 3PL 3 | 8612129 | Отменен | Кластер Краснодар | Кластер Москва |
'''Удалим дубли, оставив только первый, поскольку, я думаю, фактически заказ был сделан
в более раннюю дату.'''
first_delivery = first_delivery.drop_duplicates(subset = 'number_order', keep = 'first')
'''Также мы не сможем использовать для анализа заказы, в которых даты заказа нет'''
first_delivery[first_delivery['date_order'].isna()].head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 204 | NaT | 301939627 | 2021-01-03 | 1 | NaT | Самовывоз | 3PL 2 | 68503 | Доставлен | Кластер Екатеринбург | Кластер Москва |
| 595 | NaT | 2914794729 | 2021-01-03 | 1 | NaT | Самовывоз | 3PL 2 | 68503 | Отменен | Кластер Якутия | Кластер Москва |
| 624 | NaT | 3150043147 | 2021-01-10 | 1 | 2021-01-08 | Курьер | Самодоставка | 3302325 | Доставлен | Кластер Москва | Кластер Москва |
| 753 | NaT | 315195519 | 2021-01-11 | 1 | 2021-01-11 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область |
| 920 | NaT | 312463476 | 2021-01-05 | 1 | 2021-01-02 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва |
'''Их всего 33 штуки, можно их удалить'''
first_delivery[first_delivery['date_order'].isna()].shape
(33, 11)
'''Удаляем NaN и оставляем только нужные колонки'''
first_delivery = first_delivery[first_delivery['date_order'].notna()]
first_delivery = first_delivery[['date_order', 'planned_data_order',
'carrier_name', 'warehouse_cluster', 'customer_cluster']]
first_delivery.head()
| date_order | planned_data_order | carrier_name | warehouse_cluster | customer_cluster | |
|---|---|---|---|---|---|
| 0 | 2021-01-05 | 2021-01-14 | 3PL 1 | Кластер Москва | Кластер Краснодар |
| 1 | 2021-01-03 | 2021-01-11 | Самодоставка | Кластер Московская область | Кластер Московская область |
| 2 | 2021-01-08 | 2021-01-16 | Самодоставка | Кластер Московская область | Кластер Московская область |
| 3 | 2021-01-12 | 2021-01-20 | 3PL 1 | Кластер Москва | Кластер Санкт-Петербург |
| 4 | 2021-01-16 | 2021-01-26 | 3PL 1 | Кластер Санкт-Петербург | Кластер Санкт-Петербург |
'''Добавим колонку с обещанным сроком доставки в днях, а также с месяцем года и номером недели'''
first_delivery['duration'] = (first_delivery['planned_data_order'] - first_delivery['date_order']).dt.days
first_delivery['month'] = first_delivery['date_order'].dt.strftime('%Y-%m')
first_delivery['week'] = first_delivery['date_order'].apply(lambda x: x.strftime("%W"))
PromisedClick2Delivery = first_delivery
PromisedClick2Delivery.head()
| date_order | planned_data_order | carrier_name | warehouse_cluster | customer_cluster | duration | month | week | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-05 | 2021-01-14 | 3PL 1 | Кластер Москва | Кластер Краснодар | 9 | 2021-01 | 01 |
| 1 | 2021-01-03 | 2021-01-11 | Самодоставка | Кластер Московская область | Кластер Московская область | 8 | 2021-01 | 00 |
| 2 | 2021-01-08 | 2021-01-16 | Самодоставка | Кластер Московская область | Кластер Московская область | 8 | 2021-01 | 01 |
| 3 | 2021-01-12 | 2021-01-20 | 3PL 1 | Кластер Москва | Кластер Санкт-Петербург | 8 | 2021-01 | 02 |
| 4 | 2021-01-16 | 2021-01-26 | 3PL 1 | Кластер Санкт-Петербург | Кластер Санкт-Петербург | 10 | 2021-01 | 02 |
'''Думаю, что для анализа динамики будет разумно использовать среднее значение показателя
обещанного срока доставки. Начнем с динамики по месяцам. Таблица небольшая, поэтому
выглядит достаточно наглядно'''
month_PromisedClick2Delivery = PromisedClick2Delivery.groupby('month', as_index = False) \
.agg({'duration': 'mean'})
month_PromisedClick2Delivery
| month | duration | |
|---|---|---|
| 0 | 2021-01 | 7.705836 |
| 1 | 2021-02 | 7.626955 |
| 2 | 2021-03 | 7.233554 |
| 3 | 2021-04 | 6.900000 |
fig = px.line(month_PromisedClick2Delivery, x="month", y="duration",
labels = dict(month="month", duration="avg_duration"))
fig.show()
'''Динамика по неделям'''
week_PromisedClick2Delivery = PromisedClick2Delivery.groupby('week', as_index = False) \
.agg({'duration': 'mean'})
week_PromisedClick2Delivery.head()
| week | duration | |
|---|---|---|
| 0 | 00 | 9.053571 |
| 1 | 01 | 7.395062 |
| 2 | 02 | 7.689362 |
| 3 | 03 | 7.780000 |
| 4 | 04 | 7.663594 |
fig = px.line(week_PromisedClick2Delivery, x="week", y="duration",
labels = dict(week="number of week", duration="avg_duration"))
fig.show()
'''Динамика по дням'''
days_PromisedClick2Delivery = PromisedClick2Delivery.groupby('date_order', as_index = False) \
.agg({'duration': 'mean'})
days_PromisedClick2Delivery.head()
| date_order | duration | |
|---|---|---|
| 0 | 2021-01-01 | 9.285714 |
| 1 | 2021-01-02 | 9.176471 |
| 2 | 2021-01-03 | 8.840000 |
| 3 | 2021-01-04 | 8.625000 |
| 4 | 2021-01-05 | 8.333333 |
fig = px.line(days_PromisedClick2Delivery, x="date_order", y="duration",
labels = dict(date_order="date_order", duration="avg_duration"))
fig.show()
'''Сравнение перевозчиков'''
fig = px.box(PromisedClick2Delivery, x="carrier_name", y="duration")
fig.show()
'''Сравнение обещанного срока доставки из кластера в кластер. Стоило бы уточнить, в какой форме это хотели
бы увидеть, но, я думаю, таблицы здесь достаточно'''
PromisedClick2Delivery_clusters = PromisedClick2Delivery.groupby(['warehouse_cluster', 'customer_cluster']) \
.agg({'duration': 'mean'})
PromisedClick2Delivery_clusters.head()
| duration | ||
|---|---|---|
| warehouse_cluster | customer_cluster | |
| Кластер Екатеринбург | Кластер Екатеринбург | 7.505140 |
| Кластер Казань | 7.445946 | |
| Кластер Калининград | 7.500000 | |
| Кластер Краснодар | 7.614286 | |
| Кластер Москва | 7.446809 |
Посчитать Click2Delivery - метрика фактического срока доставки (от даты заказа до момента вручения), в сутках.
Необходимо сравнение с метрикой PromisedClick2Delivery;
Необходимо понимание кластеров, где заказы доставляются быстрее, чем мы обещаем клиентам;
'''Возьмем датафрейм из первого задания. В нем уже удалены недоставленые заказы и 44 доставленных,
дата доставки у которых отсутствует. '''
logistics_without_nan.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | ontime | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 920 | NaT | 312463476 | 2021-01-05 | 1 | 2021-01-02 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва | 1 |
| 918 | 2021-01-07 | 311714051 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 |
| 1676 | 2021-01-04 | 3109561443 | 2021-01-14 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 833469 | Доставлен | Кластер Казань | Кластер Екатеринбург | 1 |
| 2144 | 2021-01-05 | 3101445723 | 2021-01-12 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 989330 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург | 1 |
| 3342 | 2021-01-10 | 311729202 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 |
'''Выше мы уже разобрались, что дата заказа часто при изменении таймслота не проставляется.
Вот ниже для примера можно посмотреть. Но допустим, что где-то получилось наоборот и при первом таймслоте
даты доставки нет, а дальше где-то проставлена. Номера заказов нас интересуют уникальные, итоговая дата
доставки все равно проставляется для всех строк одна. Поэтому отсортируем дф по дате заказа,
это 'опустит' строки, в которых даты заказа нет, вниз. Затем на всякий случай по таймслоту
и удалим дубликаты номеров заказов, оставив первый'''
logistics_without_nan[logistics_without_nan['number_order'] == 3514710433]
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | ontime | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6070 | 2021-03-03 | 3514710433 | 2021-03-11 | 1 | 2021-03-15 | Курьер | 3PL 3 | 19923 | Доставлен | Кластер Центр | Кластер Московская область | 0 |
| 6067 | NaT | 3514710433 | 2021-03-12 | 2 | 2021-03-15 | Курьер | 3PL 3 | 19923 | Доставлен | Кластер Центр | Кластер Московская область | 0 |
| 6069 | NaT | 3514710433 | 2021-03-13 | 3 | 2021-03-15 | Курьер | 3PL 3 | 19923 | Доставлен | Кластер Центр | Кластер Московская область | 0 |
| 6068 | NaT | 3514710433 | 2021-03-15 | 4 | 2021-03-15 | Курьер | 3PL 3 | 19923 | Доставлен | Кластер Центр | Кластер Московская область | 1 |
table_Click2Delivery = logistics_without_nan.sort_values(['date_order', 'timeslot_number']) \
.drop_duplicates(subset = 'number_order', keep = 'first')
table_Click2Delivery.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | ontime | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4947 | 2021-01-01 | 312307142 | 2021-01-08 | 1 | 2021-01-04 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Москва | Кластер Московская область | 1 |
| 3376 | 2021-01-01 | 3132714147 | 2021-01-12 | 1 | 2021-01-06 | Самовывоз | 3PL 2 | 41958 | Доставлен | Кластер Санкт-Петербург | Кластер Казань | 1 |
| 4144 | 2021-01-01 | 3141410151 | 2021-01-10 | 1 | 2021-01-08 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Москва | Кластер Московская область | 1 |
| 5930 | 2021-01-01 | 314096365 | 2021-01-09 | 1 | 2021-01-08 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 |
| 307 | 2021-01-01 | 316360643 | 2021-01-12 | 1 | 2021-01-10 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва | 1 |
'''Все равно осталось 22 заказа с отсутствующей датой. Это немного, удалим их.'''
table_Click2Delivery.isna().sum()
date_order 22 number_order 0 planned_data_order 0 timeslot_number 0 result_data_order 0 delivery_type 0 carrier_name 0 seller_ID 0 order_status 0 customer_cluster 0 warehouse_cluster 0 ontime 0 dtype: int64
'''И оставим только нужные колонки. Непонятно, о каких кластерах идет речь в задании,
оставим и кластеры склада, и кластеры покупателей.'''
table_Click2Delivery = table_Click2Delivery.dropna()
table_Click2Delivery = table_Click2Delivery[['date_order', 'number_order', 'result_data_order',
'customer_cluster', 'warehouse_cluster']]
table_Click2Delivery.head()
| date_order | number_order | result_data_order | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|
| 4947 | 2021-01-01 | 312307142 | 2021-01-04 | Кластер Москва | Кластер Московская область |
| 3376 | 2021-01-01 | 3132714147 | 2021-01-06 | Кластер Санкт-Петербург | Кластер Казань |
| 4144 | 2021-01-01 | 3141410151 | 2021-01-08 | Кластер Москва | Кластер Московская область |
| 5930 | 2021-01-01 | 314096365 | 2021-01-08 | Кластер Московская область | Кластер Московская область |
| 307 | 2021-01-01 | 316360643 | 2021-01-10 | Кластер Москва | Кластер Москва |
'''Добавим колонку с фактическим сроком доставки в днях.
Видим очень странную вещь, есть заказы, дата офрмления которых позже, чем их дата доставки.
Я посмотрела на них в изначальном датафрейме, да, все так. Даты распарсены верно, заказы уникальные,
мы ничего лишнего не удалили.'''
table_Click2Delivery['delivery_time'] = (table_Click2Delivery['result_data_order'] -
table_Click2Delivery['date_order']).dt.days
table_Click2Delivery.head()
| date_order | number_order | result_data_order | customer_cluster | warehouse_cluster | delivery_time | |
|---|---|---|---|---|---|---|
| 4947 | 2021-01-01 | 312307142 | 2021-01-04 | Кластер Москва | Кластер Московская область | 3 |
| 3376 | 2021-01-01 | 3132714147 | 2021-01-06 | Кластер Санкт-Петербург | Кластер Казань | 5 |
| 4144 | 2021-01-01 | 3141410151 | 2021-01-08 | Кластер Москва | Кластер Московская область | 7 |
| 5930 | 2021-01-01 | 314096365 | 2021-01-08 | Кластер Московская область | Кластер Московская область | 7 |
| 307 | 2021-01-01 | 316360643 | 2021-01-10 | Кластер Москва | Кластер Москва | 9 |
'''Их много, почти 1000 штук. Но использовать их для анализа я не могу, отрицательные
значения исказят результат.'''
table_Click2Delivery[table_Click2Delivery['delivery_time'] < 0]
| date_order | number_order | result_data_order | customer_cluster | warehouse_cluster | delivery_time | |
|---|---|---|---|---|---|---|
| 1676 | 2021-01-04 | 3109561443 | 2021-01-03 | Кластер Казань | Кластер Екатеринбург | -1 |
| 2144 | 2021-01-05 | 3101445723 | 2021-01-03 | Кластер Санкт-Петербург | Кластер Екатеринбург | -2 |
| 6552 | 2021-01-05 | 309553962 | 2021-01-03 | Кластер Казань | Кластер Екатеринбург | -2 |
| 4602 | 2021-01-05 | 309966599 | 2021-01-04 | Кластер Казань | Кластер Екатеринбург | -1 |
| 918 | 2021-01-07 | 311714051 | 2021-01-03 | Кластер Московская область | Кластер Московская область | -4 |
| ... | ... | ... | ... | ... | ... | ... |
| 5627 | 2021-03-27 | 346766153 | 2021-03-11 | Кластер Екатеринбург | Кластер Екатеринбург | -16 |
| 3727 | 2021-04-01 | 3494931462 | 2021-03-11 | Кластер Казань | Кластер Екатеринбург | -21 |
| 2171 | 2021-04-02 | 3522714690 | 2021-03-15 | Кластер Московская область | Кластер Екатеринбург | -18 |
| 2653 | 2021-04-05 | 352137569 | 2021-03-15 | Кластер Казань | Кластер Екатеринбург | -21 |
| 5691 | 2021-04-07 | 3640171453 | 2021-03-14 | Кластер Москва | Кластер Московская область | -24 |
916 rows × 6 columns
table_Click2Delivery = table_Click2Delivery[table_Click2Delivery['delivery_time'] >= 0]
table_Click2Delivery['month'] = table_Click2Delivery['date_order'].dt.strftime('%Y-%m')
table_Click2Delivery['week'] = table_Click2Delivery['date_order'].apply(lambda x: x.strftime("%W"))
table_Click2Delivery.head()
| date_order | number_order | result_data_order | customer_cluster | warehouse_cluster | delivery_time | month | week | |
|---|---|---|---|---|---|---|---|---|
| 4947 | 2021-01-01 | 312307142 | 2021-01-04 | Кластер Москва | Кластер Московская область | 3 | 2021-01 | 00 |
| 3376 | 2021-01-01 | 3132714147 | 2021-01-06 | Кластер Санкт-Петербург | Кластер Казань | 5 | 2021-01 | 00 |
| 4144 | 2021-01-01 | 3141410151 | 2021-01-08 | Кластер Москва | Кластер Московская область | 7 | 2021-01 | 00 |
| 5930 | 2021-01-01 | 314096365 | 2021-01-08 | Кластер Московская область | Кластер Московская область | 7 | 2021-01 | 00 |
| 307 | 2021-01-01 | 316360643 | 2021-01-10 | Кластер Москва | Кластер Москва | 9 | 2021-01 | 00 |
'''Посчитаем динамику по дням и сравним ее с метрикой PromisedClick2Delivery'''
days_Click2Delivery = table_Click2Delivery.groupby('date_order', as_index = False) \
.agg({'delivery_time': 'mean'})
days_Click2Delivery = days_Click2Delivery.merge(days_PromisedClick2Delivery, how = 'left', on = 'date_order') \
.rename(columns={'duration': 'planned_delivery'})
days_Click2Delivery.head()
| date_order | delivery_time | planned_delivery | |
|---|---|---|---|
| 0 | 2021-01-01 | 8.000000 | 9.285714 |
| 1 | 2021-01-02 | 7.571429 | 9.176471 |
| 2 | 2021-01-03 | 6.700000 | 8.840000 |
| 3 | 2021-01-04 | 6.000000 | 8.625000 |
| 4 | 2021-01-05 | 7.545455 | 8.333333 |
df = pd.melt(days_Click2Delivery, id_vars=['date_order'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='date_order', y='value', color='variable')
fig.show()
'''Посчитаем динамику по месяцам и сравним ее с метрикой PromisedClick2Delivery'''
month_Click2Delivery = table_Click2Delivery.groupby('month', as_index = False) \
.agg({'delivery_time': 'mean'})
month_Click2Delivery = month_Click2Delivery.merge(month_PromisedClick2Delivery, how = 'left', on = 'month') \
.rename(columns={'duration': 'planned_delivery'})
month_Click2Delivery.head()
| month | delivery_time | planned_delivery | |
|---|---|---|---|
| 0 | 2021-01 | 6.087862 | 7.705836 |
| 1 | 2021-02 | 6.324035 | 7.626955 |
| 2 | 2021-03 | 5.228602 | 7.233554 |
df = pd.melt(month_Click2Delivery, id_vars=['month'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='month', y='value', color='variable')
fig.show()
'''И, наконец, по неделям'''
week_Click2Delivery = table_Click2Delivery.groupby('week', as_index = False) \
.agg({'delivery_time': 'mean'})
week_Click2Delivery = week_Click2Delivery.merge(week_PromisedClick2Delivery, how = 'left', on = 'week') \
.rename(columns={'duration': 'planned_delivery'})
week_Click2Delivery.head()
| week | delivery_time | planned_delivery | |
|---|---|---|---|
| 0 | 00 | 7.272727 | 9.053571 |
| 1 | 01 | 6.308824 | 7.395062 |
| 2 | 02 | 6.203046 | 7.689362 |
| 3 | 03 | 6.037175 | 7.780000 |
| 4 | 04 | 5.815385 | 7.663594 |
df = pd.melt(week_Click2Delivery, id_vars=['week'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='week', y='value', color='variable')
fig.show()
'''Вот таблица из прошлого задания, в которой видно обещанные сроки доставки из
кластера в кластер'''
PromisedClick2Delivery_clusters = PromisedClick2Delivery_clusters.reset_index()
PromisedClick2Delivery_clusters.head()
| warehouse_cluster | customer_cluster | duration | |
|---|---|---|---|
| 0 | Кластер Екатеринбург | Кластер Екатеринбург | 7.505140 |
| 1 | Кластер Екатеринбург | Кластер Казань | 7.445946 |
| 2 | Кластер Екатеринбург | Кластер Калининград | 7.500000 |
| 3 | Кластер Екатеринбург | Кластер Краснодар | 7.614286 |
| 4 | Кластер Екатеринбург | Кластер Москва | 7.446809 |
'''Сделаем такую же для фактического срока доставки'''
Click2Delivery_clusters = table_Click2Delivery.groupby(['warehouse_cluster', 'customer_cluster']) \
.agg({'delivery_time': 'mean'}).reset_index()
Click2Delivery_clusters.head()
| warehouse_cluster | customer_cluster | delivery_time | |
|---|---|---|---|
| 0 | Кластер Екатеринбург | Кластер Екатеринбург | 2.187500 |
| 1 | Кластер Екатеринбург | Кластер Казань | 3.434783 |
| 2 | Кластер Екатеринбург | Кластер Калининград | 4.500000 |
| 3 | Кластер Екатеринбург | Кластер Краснодар | 3.843137 |
| 4 | Кластер Екатеринбург | Кластер Москва | 5.447368 |
'''И смерджим их'''
delivery_clusters = Click2Delivery_clusters.merge(PromisedClick2Delivery_clusters,
how = 'left', on = ['warehouse_cluster', 'customer_cluster']) \
.rename(columns={'duration': 'planned_delivery_time'})
delivery_clusters.head()
| warehouse_cluster | customer_cluster | delivery_time | planned_delivery_time | |
|---|---|---|---|---|
| 0 | Кластер Екатеринбург | Кластер Екатеринбург | 2.187500 | 7.505140 |
| 1 | Кластер Екатеринбург | Кластер Казань | 3.434783 | 7.445946 |
| 2 | Кластер Екатеринбург | Кластер Калининград | 4.500000 | 7.500000 |
| 3 | Кластер Екатеринбург | Кластер Краснодар | 3.843137 | 7.614286 |
| 4 | Кластер Екатеринбург | Кластер Москва | 5.447368 | 7.446809 |
'''Оставим только те строки, где фактическое время доставки меньше планового'''
delivery_clusters = delivery_clusters[delivery_clusters['delivery_time']
< delivery_clusters['planned_delivery_time']]
delivery_clusters.head()
| warehouse_cluster | customer_cluster | delivery_time | planned_delivery_time | |
|---|---|---|---|---|
| 0 | Кластер Екатеринбург | Кластер Екатеринбург | 2.187500 | 7.505140 |
| 1 | Кластер Екатеринбург | Кластер Казань | 3.434783 | 7.445946 |
| 2 | Кластер Екатеринбург | Кластер Калининград | 4.500000 | 7.500000 |
| 3 | Кластер Екатеринбург | Кластер Краснодар | 3.843137 | 7.614286 |
| 4 | Кластер Екатеринбург | Кластер Москва | 5.447368 | 7.446809 |
Посчитать GMV Accepted - метрика совокупного объема продаж (считается по заказам, оформленным в указанный день), в рублях.
Необходимо видеть изменение метрики по дням;
Необходимы разрезы по перевозчикам, по типу доставки, по кластерам склада;
q = '''
SELECT *
FROM
{db}.product_online
'''
'''Информацию о суммах заказов будем брать из таблицы product_online'''
product_online = ph.read_clickhouse(query=q, connection=connection_default)
product_online.head()
| number_order | product_ID | product_price | amount | |
|---|---|---|---|---|
| 0 | 313655114 | 983587801 | 5512.44 | 1 |
| 1 | 313655114 | 983587158 | 4070.35 | 2 |
| 2 | 313655114 | 983582669 | 3591.97 | 1 |
| 3 | 315229153 | 983589424 | 4433.23 | 3 |
| 4 | 315229153 | 983587827 | 2862.50 | 2 |
pandas_profiling.ProfileReport(product_online)
Summarize dataset: 0%| | 0/18 [00:00<?, ?it/s]
Generate report structure: 0%| | 0/1 [00:00<?, ?it/s]
Render HTML: 0%| | 0/1 [00:00<?, ?it/s]
'''Перемножив цену на количество товара, получим столбец с суммой по каждому товару в заказе'''
product_online['product_cost'] = product_online['amount'] * product_online['product_price']
product_online.head()
| number_order | product_ID | product_price | amount | product_cost | |
|---|---|---|---|---|---|
| 0 | 313655114 | 983587801 | 5512.44 | 1 | 5512.44 |
| 1 | 313655114 | 983587158 | 4070.35 | 2 | 8140.70 |
| 2 | 313655114 | 983582669 | 3591.97 | 1 | 3591.97 |
| 3 | 315229153 | 983589424 | 4433.23 | 3 | 13299.69 |
| 4 | 315229153 | 983587827 | 2862.50 | 2 | 5725.00 |
'''И теперь посчитаем стоимость каждого заказа'''
product_online = product_online.groupby('number_order', as_index = False) \
.agg({'product_cost': 'sum'}).rename(columns = {'product_cost': 'order_sum'})
product_online.head()
| number_order | order_sum | |
|---|---|---|
| 0 | 300232595 | 13049.90 |
| 1 | 300612175 | 21582.16 |
| 2 | 301939627 | 16359.44 |
| 3 | 302073096 | 13328.32 |
| 4 | 302459167 | 8701.56 |
'''Далее нам нужна первая таблица, но нужно удалить из нее лишнее'''
logistics_online.shape
(6596, 11)
'''В контексте данного задания нас интересует дата заказа. Помним, что часто дата заказа
проставляется только в строке с его номером и первым таймслотом. Отсортируем дф по дате заказа,
таким образом все даты, в которых стоит NaN, опустятся в конец дф. Затем отсортируем на всякий
случай по возрастанию таймслота и удалим дубликаты номеров заказов.Можем так сделать,
поскольку нас интересуют уникальные заказы, а в дф часто один заказ встречается несколько раз.'''
table_for_GMV_Accepted = logistics_online.sort_values(['date_order', 'timeslot_number']) \
.drop_duplicates(subset = 'number_order', keep = 'first')
table_for_GMV_Accepted.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 307 | 2021-01-01 | 316360643 | 2021-01-12 | 1 | 2021-01-10 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва |
| 1146 | 2021-01-01 | 30361461496 | 2021-01-05 | 1 | 2021-01-11 | Курьер | Самодоставка | 833469 | Доставлен | Кластер Московская область | Кластер Екатеринбург |
| 1281 | 2021-01-01 | 315409997 | 2021-01-12 | 1 | 2021-01-11 | Самовывоз | 3PL 2 | 41958 | Доставлен | Кластер Московская область | Кластер Казань |
| 1333 | 2021-01-01 | 311096952 | 2021-01-12 | 1 | NaT | Курьер | 3PL 3 | 19 | Доставлен | Кластер Центр | Кластер Москва |
| 2234 | 2021-01-01 | 31211414196 | 2021-01-12 | 1 | 2021-01-10 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Москва | Кластер Москва |
'''Все равно остался 41 заказ без даты, это менее одного процента данных, можно их удалить'''
table_for_GMV_Accepted.isna().sum()
date_order 41 number_order 0 planned_data_order 0 timeslot_number 0 result_data_order 860 delivery_type 0 carrier_name 0 seller_ID 0 order_status 0 customer_cluster 0 warehouse_cluster 0 dtype: int64
table_for_GMV_Accepted = table_for_GMV_Accepted[table_for_GMV_Accepted['date_order'].notna()]
'''Теперь можем смерджить две таблицы, таким образом получив стоимость всех заказов'''
GMV_Accepted_merged = table_for_GMV_Accepted.merge(product_online, how = 'left',
on = 'number_order')
GMV_Accepted_merged.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | order_sum | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2021-01-01 | 316360643 | 2021-01-12 | 1 | 2021-01-10 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва | 26419.40 |
| 1 | 2021-01-01 | 30361461496 | 2021-01-05 | 1 | 2021-01-11 | Курьер | Самодоставка | 833469 | Доставлен | Кластер Московская область | Кластер Екатеринбург | 19632.88 |
| 2 | 2021-01-01 | 315409997 | 2021-01-12 | 1 | 2021-01-11 | Самовывоз | 3PL 2 | 41958 | Доставлен | Кластер Московская область | Кластер Казань | 21017.96 |
| 3 | 2021-01-01 | 311096952 | 2021-01-12 | 1 | NaT | Курьер | 3PL 3 | 19 | Доставлен | Кластер Центр | Кластер Москва | 46305.76 |
| 4 | 2021-01-01 | 31211414196 | 2021-01-12 | 1 | 2021-01-10 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Москва | Кластер Москва | 33194.08 |
'''Все хорошо, суммы есть для всех заказов'''
GMV_Accepted_merged.isna().sum()
date_order 0 number_order 0 planned_data_order 0 timeslot_number 0 result_data_order 841 delivery_type 0 carrier_name 0 seller_ID 0 order_status 0 customer_cluster 0 warehouse_cluster 0 order_sum 0 dtype: int64
'''Всё, можно формировать финальный датафрейм'''
GMV_Accepted = GMV_Accepted_merged.groupby(['date_order', 'carrier_name',
'delivery_type', 'warehouse_cluster'], as_index = False) \
.agg({'order_sum': 'sum'})
GMV_Accepted.head()
| date_order | carrier_name | delivery_type | warehouse_cluster | order_sum | |
|---|---|---|---|---|---|
| 0 | 2021-01-01 | 3PL 1 | Самовывоз | Кластер Москва | 36401.06 |
| 1 | 2021-01-01 | 3PL 2 | Самовывоз | Кластер Казань | 30216.66 |
| 2 | 2021-01-01 | 3PL 3 | Курьер | Кластер Москва | 46305.76 |
| 3 | 2021-01-01 | 3PL 3 | Самовывоз | Кластер Москва | 4904.88 |
| 4 | 2021-01-01 | Самодоставка | Курьер | Кластер Екатеринбург | 25686.88 |
'''Динамика по датам заказа. Не слишком информативный график,
но благодаря возможностям plotly можно просто 'приблизить' нужный период'''
fig = px.line(GMV_Accepted, x="date_order", y="order_sum")
fig.show()
'''Динамика в разрезе перевозчиков'''
fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'carrier_name')
fig.show()
'''Динамика в разрезе типов доставки'''
fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'delivery_type')
fig.show()
'''Динамика в разрезе кластеров склада'''
fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'warehouse_cluster')
fig.show()
Посчитать Return Rate - доля возвратов (отношение количества возвратов к заказам в этот день), в %.
Необходимо видеть изменение метрики по дням;
Необходимы разрезы по перевозчикам, по кластерам склада и клиента;
q = '''
SELECT *
FROM
{db}.returns_online
'''
'''Видим, что в таблице с возвратами есть некорректные даты.
Это не проблема pandahouse, они такие в бд'''
returns_online = ph.read_clickhouse(query=q, connection=connection_default)
returns_online.head()
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 0 | 3031410942 | 983588011 | 1 | 1970-01-01 |
| 1 | 31014374140 | 983586554 | 1 | 2021-07-01 |
| 2 | 3147145173 | 983589156 | 1 | 2021-11-01 |
| 3 | 3141464669 | 983589362 | 1 | 2021-11-01 |
| 4 | 3131450699 | 983589729 | 3 | 2021-12-01 |
pandas_profiling.ProfileReport(returns_online)
Summarize dataset: 0%| | 0/18 [00:00<?, ?it/s]
Generate report structure: 0%| | 0/1 [00:00<?, ?it/s]
Render HTML: 0%| | 0/1 [00:00<?, ?it/s]
'''Разберемся с дубликатами'''
returns_online[returns_online.duplicated()]
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 5 | 3131450699 | 983589729 | 3 | 2021-12-01 |
| 9 | 3155331412 | 983580141 | 1 | 1970-01-01 |
| 16 | 3114571009 | 983586796 | 3 | 1970-01-01 |
| 19 | 312396246 | 983582111 | 1 | 1970-01-01 |
| 22 | 312396246 | 983588915 | 1 | 1970-01-01 |
| ... | ... | ... | ... | ... |
| 488 | 35144201422 | 983588149 | 1 | 1970-01-01 |
| 489 | 3600535146 | 983582199 | 4 | 1970-01-01 |
| 490 | 3624014609 | 983589024 | 1 | 1970-01-01 |
| 493 | 35714135014 | 983587402 | 2 | 1970-01-01 |
| 495 | 35714135014 | 983584567 | 3 | 1970-01-01 |
100 rows × 4 columns
'''Посмотрим на какой-нибудь. Действительно дубликат. Придется их удалять. В данном задании
они ни на что не влияют, а вот в следующем это будет важно'''
returns_online[returns_online['number_order']==3131450699]
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 4 | 3131450699 | 983589729 | 3 | 2021-12-01 |
| 5 | 3131450699 | 983589729 | 3 | 2021-12-01 |
returns_online = returns_online.drop_duplicates()
'''Вернемся к датам. Это тоже придется удалять. 181 строка'''
returns_online[returns_online['return_date'] == '1970-01-01'].head()
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 0 | 3031410942 | 983588011 | 1 | 1970-01-01 |
| 7 | 315195519 | 983587637 | 1 | 1970-01-01 |
| 8 | 3155331412 | 983580141 | 1 | 1970-01-01 |
| 10 | 312396246 | 983582111 | 1 | 1970-01-01 |
| 11 | 312396246 | 983583606 | 1 | 1970-01-01 |
'''Получаем корректный датафрейм с возвратами. Без дубликатов и некорректных дат'''
returns_online = returns_online[returns_online['return_date'] != '1970-01-01']
returns_online.head()
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 1 | 31014374140 | 983586554 | 1 | 2021-07-01 |
| 2 | 3147145173 | 983589156 | 1 | 2021-11-01 |
| 3 | 3141464669 | 983589362 | 1 | 2021-11-01 |
| 4 | 3131450699 | 983589729 | 3 | 2021-12-01 |
| 6 | 315774746 | 983585035 | 1 | 2021-12-01 |
returns_number = returns_online.groupby('return_date', as_index = False).agg({'number_order': 'nunique'}) \
.rename(columns={'number_order': 'returns_number'})
returns_number.head()
| return_date | returns_number | |
|---|---|---|
| 0 | 2021-01-02 | 7 |
| 1 | 2021-01-03 | 1 |
| 2 | 2021-02-02 | 5 |
| 3 | 2021-02-03 | 5 |
| 4 | 2021-03-02 | 1 |
'''Посмотрим, что получилось. Ну, от 1 до 14 возвратов вполне может быть. Почему-то пики всегда 3 числа)'''
fig = px.line(returns_number, x="return_date", y="returns_number")
fig.show()
'''Теперь чтобы понимать для каждого возврата перевозчика, кластеры склада и клиента,
нужно примерджить сюда таблицу с заказами и оставить только нужные колонки'''
full_returns = returns_online.merge(logistics_online, how = 'left', on = 'number_order')
full_returns_t = full_returns[['return_date', 'number_order', 'carrier_name', 'customer_cluster',
'warehouse_cluster']]
full_returns_t.head()
| return_date | number_order | carrier_name | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|
| 0 | 2021-07-01 | 31014374140 | 3PL 2 | Кластер Московская область | Кластер Екатеринбург |
| 1 | 2021-11-01 | 3147145173 | Самодоставка | Кластер Московская область | Кластер Московская область |
| 2 | 2021-11-01 | 3141464669 | 3PL 2 | Кластер Центр | Кластер Казань |
| 3 | 2021-12-01 | 3131450699 | 3PL 2 | Кластер Казань | Кластер Екатеринбург |
| 4 | 2021-12-01 | 315774746 | 3PL 1 | Кластер Московская область | Кластер Москва |
full_returns_final = full_returns_t.groupby(['return_date', 'carrier_name',
'customer_cluster', 'warehouse_cluster'],
as_index = False) \
.agg({'number_order': 'nunique'}) \
.rename(columns={'number_order': 'returns_number'})
full_returns_final.head()
| return_date | carrier_name | customer_cluster | warehouse_cluster | returns_number | |
|---|---|---|---|---|---|
| 0 | 2021-01-02 | 3PL 1 | Кластер Екатеринбург | Кластер Москва | 1 |
| 1 | 2021-01-02 | 3PL 1 | Кластер Москва | Кластер Екатеринбург | 1 |
| 2 | 2021-01-02 | 3PL 1 | Кластер Санкт-Петербург | Кластер Екатеринбург | 1 |
| 3 | 2021-01-02 | 3PL 2 | Кластер Калининград | Кластер Екатеринбург | 1 |
| 4 | 2021-01-02 | 3PL 4 | Кластер Московская область | Кластер Казань | 1 |
'''Возьмем таблицу из предыдущего задания. Это просто все имеющиеся заказы без дублей
и без NaN значений'''
table_for_GMV_Accepted.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 307 | 2021-01-01 | 316360643 | 2021-01-12 | 1 | 2021-01-10 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва |
| 1146 | 2021-01-01 | 30361461496 | 2021-01-05 | 1 | 2021-01-11 | Курьер | Самодоставка | 833469 | Доставлен | Кластер Московская область | Кластер Екатеринбург |
| 1281 | 2021-01-01 | 315409997 | 2021-01-12 | 1 | 2021-01-11 | Самовывоз | 3PL 2 | 41958 | Доставлен | Кластер Московская область | Кластер Казань |
| 1333 | 2021-01-01 | 311096952 | 2021-01-12 | 1 | NaT | Курьер | 3PL 3 | 19 | Доставлен | Кластер Центр | Кластер Москва |
| 2234 | 2021-01-01 | 31211414196 | 2021-01-12 | 1 | 2021-01-10 | Самовывоз | 3PL 1 | 301202 | Доставлен | Кластер Москва | Кластер Москва |
'''Сгруппируем ее как просят в задании'''
table_for_Return_Rate = table_for_GMV_Accepted.groupby(['date_order', 'carrier_name',
'customer_cluster', 'warehouse_cluster'],
as_index = False) \
.agg({'number_order': 'nunique'}) \
.rename(columns={'number_order': 'order_number'})
table_for_Return_Rate.head()
| date_order | carrier_name | customer_cluster | warehouse_cluster | order_number | |
|---|---|---|---|---|---|
| 0 | 2021-01-01 | 3PL 1 | Кластер Москва | Кластер Москва | 1 |
| 1 | 2021-01-01 | 3PL 1 | Кластер Московская область | Кластер Москва | 1 |
| 2 | 2021-01-01 | 3PL 2 | Кластер Московская область | Кластер Казань | 1 |
| 3 | 2021-01-01 | 3PL 2 | Кластер Санкт-Петербург | Кластер Казань | 1 |
| 4 | 2021-01-01 | 3PL 3 | Кластер Санкт-Петербург | Кластер Москва | 1 |
'Сформируем финальный датафрейм. В нем можно посмотреть данные во всех запрашиваемых разрезах'
Return_Rate = pd.merge(table_for_Return_Rate, full_returns_final, how = 'left',
left_on = ['date_order', 'carrier_name', 'customer_cluster', 'warehouse_cluster'],
right_on = ['return_date', 'carrier_name', 'customer_cluster', 'warehouse_cluster']) \
.fillna(0).drop('return_date', axis = 1)
Return_Rate['r_r_%'] = Return_Rate['returns_number'] *100 / Return_Rate['order_number']
Return_Rate.head()
| date_order | carrier_name | customer_cluster | warehouse_cluster | order_number | returns_number | r_r_% | |
|---|---|---|---|---|---|---|---|
| 0 | 2021-01-01 | 3PL 1 | Кластер Москва | Кластер Москва | 1 | 0.0 | 0.0 |
| 1 | 2021-01-01 | 3PL 1 | Кластер Московская область | Кластер Москва | 1 | 0.0 | 0.0 |
| 2 | 2021-01-01 | 3PL 2 | Кластер Московская область | Кластер Казань | 1 | 0.0 | 0.0 |
| 3 | 2021-01-01 | 3PL 2 | Кластер Санкт-Петербург | Кластер Казань | 1 | 0.0 | 0.0 |
| 4 | 2021-01-01 | 3PL 3 | Кластер Санкт-Петербург | Кластер Москва | 1 | 0.0 | 0.0 |
'''Посмотрим динамику по дням на графике'''
dates_Return_Rate = table_for_GMV_Accepted.groupby('date_order', as_index = False) \
.agg({'number_order': 'nunique'}) \
.rename(columns={'number_order': 'order_number'}) \
.merge(returns_number, how = 'left', left_on = 'date_order',
right_on = 'return_date').drop('return_date', axis = 1) \
.fillna(0)
dates_Return_Rate['r_r'] = dates_Return_Rate['returns_number'] * 100 / dates_Return_Rate['order_number']
dates_Return_Rate.head()
| date_order | order_number | returns_number | r_r | |
|---|---|---|---|---|
| 0 | 2021-01-01 | 14 | 0.0 | 0.000000 |
| 1 | 2021-01-02 | 17 | 7.0 | 41.176471 |
| 2 | 2021-01-03 | 25 | 1.0 | 4.000000 |
| 3 | 2021-01-04 | 32 | 0.0 | 0.000000 |
| 4 | 2021-01-05 | 27 | 0.0 | 0.000000 |
'''Огромный всплеск 3 апреля'''
fig = px.line(dates_Return_Rate, x="date_order", y="r_r")
fig.show()
'''466 процентов'''
dates_Return_Rate[dates_Return_Rate['date_order']=='2021-04-03']
| date_order | order_number | returns_number | r_r | |
|---|---|---|---|---|
| 91 | 2021-04-03 | 3 | 14.0 | 466.666667 |
'''убедимся, что ошибка не у нас в расчетах. В этот день было 3 заказа'''
logistics_online[logistics_online['date_order']=='2021-04-03']
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 969 | 2021-04-03 | 3651761467 | 2021-04-10 | 1 | NaT | Курьер | Самодоставка | 2312360 | Ожидает сборки | Кластер Краснодар | Кластер Московская область |
| 1837 | 2021-04-03 | 3514914196 | 2021-04-10 | 1 | NaT | Курьер | Самодоставка | 120629 | На пути к клиенту | Кластер Краснодар | Кластер Екатеринбург |
| 2133 | 2021-04-03 | 35623141440 | 2021-04-13 | 1 | NaT | Курьер | Самодоставка | 120629 | На пути к клиенту | Кластер Краснодар | Кластер Екатеринбург |
'''И 14 возвратов. Всё верно'''
returns_online[returns_online['return_date']=='2021-04-03']['number_order'].nunique()
14
'''Динамика в разрезе поставщиков'''
carriers_Return_Rate = Return_Rate.groupby(['date_order', 'carrier_name'], as_index = False) \
.agg({'order_number': 'sum', 'returns_number': 'sum'})
carriers_Return_Rate['r_r'] = carriers_Return_Rate['returns_number'] * 100 / carriers_Return_Rate['order_number']
carriers_Return_Rate.head()
| date_order | carrier_name | order_number | returns_number | r_r | |
|---|---|---|---|---|---|
| 0 | 2021-01-01 | 3PL 1 | 2 | 0.0 | 0.0 |
| 1 | 2021-01-01 | 3PL 2 | 2 | 0.0 | 0.0 |
| 2 | 2021-01-01 | 3PL 3 | 2 | 0.0 | 0.0 |
| 3 | 2021-01-01 | Самодоставка | 8 | 0.0 | 0.0 |
| 4 | 2021-01-02 | 3PL 1 | 5 | 0.0 | 0.0 |
'Всплеск из-за самодоставки'
fig = px.line(carriers_Return_Rate, x="date_order", y="r_r", color = 'carrier_name')
fig.show()
'''Динамика в разрезе кластеров склада'''
warehouse_Return_Rate = Return_Rate.groupby(['date_order', 'warehouse_cluster'], as_index = False) \
.agg({'order_number': 'sum', 'returns_number': 'sum'})
warehouse_Return_Rate['r_r'] = warehouse_Return_Rate['returns_number'] * 100 / warehouse_Return_Rate['order_number']
warehouse_Return_Rate.head()
| date_order | warehouse_cluster | order_number | returns_number | r_r | |
|---|---|---|---|---|---|
| 0 | 2021-01-01 | Кластер Екатеринбург | 2 | 0.0 | 0.0 |
| 1 | 2021-01-01 | Кластер Казань | 2 | 0.0 | 0.0 |
| 2 | 2021-01-01 | Кластер Москва | 6 | 0.0 | 0.0 |
| 3 | 2021-01-01 | Кластер Московская область | 4 | 0.0 | 0.0 |
| 4 | 2021-01-02 | Кластер Екатеринбург | 2 | 0.0 | 0.0 |
fig = px.line(warehouse_Return_Rate, x="date_order", y="r_r", color = 'warehouse_cluster')
fig.show()
'''Динамика в разрезе кластеров покупателя'''
customer_Return_Rate = Return_Rate.groupby(['date_order', 'customer_cluster'], as_index = False) \
.agg({'order_number': 'sum', 'returns_number': 'sum'})
customer_Return_Rate['r_r'] = customer_Return_Rate['returns_number'] * 100 / customer_Return_Rate['order_number']
customer_Return_Rate.head()
| date_order | customer_cluster | order_number | returns_number | r_r | |
|---|---|---|---|---|---|
| 0 | 2021-01-01 | Кластер Краснодар | 1 | 0.0 | 0.0 |
| 1 | 2021-01-01 | Кластер Москва | 6 | 0.0 | 0.0 |
| 2 | 2021-01-01 | Кластер Московская область | 4 | 0.0 | 0.0 |
| 3 | 2021-01-01 | Кластер Санкт-Петербург | 2 | 0.0 | 0.0 |
| 4 | 2021-01-01 | Кластер Центр | 1 | 0.0 | 0.0 |
fig = px.line(customer_Return_Rate, x="date_order", y="r_r", color = 'customer_cluster')
fig.show()
Посчитать GMV D-R - метрика, отражающая совокупный объем доставленных заказов за вычетом возвратов, в рублях.
Необходимо видеть изменение метрики по дням (для всех дней, которые были раньше, чем дата расчета метрики);
Необходимы разрезы по перевозчикам, по типу доставки, по кластерам склада.
'''Возьмем уже готовый датафрейм из первого задания с доставленными заказами.'''
logistics_online_unique.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | ontime | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 920 | NaT | 312463476 | 2021-01-05 | 1 | 2021-01-02 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва | 1 |
| 918 | 2021-01-07 | 311714051 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 |
| 1676 | 2021-01-04 | 3109561443 | 2021-01-14 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 833469 | Доставлен | Кластер Казань | Кластер Екатеринбург | 1 |
| 2144 | 2021-01-05 | 3101445723 | 2021-01-12 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 989330 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург | 1 |
| 3342 | 2021-01-10 | 311729202 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 |
'''И будет нужна таблица с суммой каждого заказа'''
product_online.head()
| number_order | order_sum | |
|---|---|---|
| 0 | 300232595 | 13049.90 |
| 1 | 300612175 | 21582.16 |
| 2 | 301939627 | 16359.44 |
| 3 | 302073096 | 13328.32 |
| 4 | 302459167 | 8701.56 |
'''Смерджим их и получим сумму по каждому заказу'''
orders_sum = logistics_online_unique.merge(product_online, how = 'left', on = 'number_order')
orders_sum.head()
| date_order | number_order | planned_data_order | timeslot_number | result_data_order | delivery_type | carrier_name | seller_ID | order_status | customer_cluster | warehouse_cluster | ontime | order_sum | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | NaT | 312463476 | 2021-01-05 | 1 | 2021-01-02 | Курьер | Самодоставка | 2619 | Доставлен | Кластер Москва | Кластер Москва | 1 | 19174.83 |
| 1 | 2021-01-07 | 311714051 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 | 19733.92 |
| 2 | 2021-01-04 | 3109561443 | 2021-01-14 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 833469 | Доставлен | Кластер Казань | Кластер Екатеринбург | 1 | 43903.87 |
| 3 | 2021-01-05 | 3101445723 | 2021-01-12 | 1 | 2021-01-03 | Самовывоз | 3PL 2 | 989330 | Доставлен | Кластер Санкт-Петербург | Кластер Екатеринбург | 1 | 3674.92 |
| 4 | 2021-01-10 | 311729202 | 2021-01-14 | 1 | 2021-01-03 | Курьер | Самодоставка | 40560 | Доставлен | Кластер Московская область | Кластер Московская область | 1 | 861.20 |
'''Сделаем из него сразу основу для финального датафрейма'''
table_for_GMV_D_R = orders_sum.groupby(['result_data_order', 'carrier_name',
'delivery_type', 'warehouse_cluster'],
as_index = False) \
.agg({'order_sum': 'sum'}) \
.rename(columns = {'result_data_order': 'date'})
table_for_GMV_D_R.head()
| date | carrier_name | delivery_type | warehouse_cluster | order_sum | |
|---|---|---|---|---|---|
| 0 | 2021-01-02 | Самодоставка | Курьер | Кластер Москва | 19174.83 |
| 1 | 2021-01-03 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 57859.05 |
| 2 | 2021-01-03 | Самодоставка | Курьер | Кластер Московская область | 49150.79 |
| 3 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 166324.08 |
| 4 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Москва | 30159.34 |
'''Теперь будет нужна таблица с возвратами. Необходимо узнать сумму каждого возврата'''
returns_online.head()
| number_order | product_ID | amount_return | return_date | |
|---|---|---|---|---|
| 1 | 31014374140 | 983586554 | 1 | 2021-07-01 |
| 2 | 3147145173 | 983589156 | 1 | 2021-11-01 |
| 3 | 3141464669 | 983589362 | 1 | 2021-11-01 |
| 4 | 3131450699 | 983589729 | 3 | 2021-12-01 |
| 6 | 315774746 | 983585035 | 1 | 2021-12-01 |
'''Для этого снова нужна таблица product_online, в ней есть цены для каждого товара. Считаем ее заново'''
q = '''
SELECT *
FROM
{db}.product_online
'''
products_for_GMV_DR = ph.read_clickhouse(query=q, connection=connection_default)
products_for_GMV_DR.head()
| number_order | product_ID | product_price | amount | |
|---|---|---|---|---|
| 0 | 313655114 | 983587801 | 5512.44 | 1 |
| 1 | 313655114 | 983587158 | 4070.35 | 2 |
| 2 | 313655114 | 983582669 | 3591.97 | 1 |
| 3 | 315229153 | 983589424 | 4433.23 | 3 |
| 4 | 315229153 | 983587827 | 2862.50 | 2 |
'''Получим сумму каждого возврата'''
returns_for_GMV_D_R = returns_online.merge(products_for_GMV_DR,
how = 'left', on = ['number_order', 'product_ID']) \
.drop('amount', axis = 1)
returns_for_GMV_D_R['return_sum'] = returns_for_GMV_D_R['amount_return'] * returns_for_GMV_D_R['product_price']
returns_for_GMV_D_R = returns_for_GMV_D_R.groupby(['return_date', 'number_order']
, as_index = False).agg({'return_sum': 'sum'})
returns_for_GMV_D_R.head()
| return_date | number_order | return_sum | |
|---|---|---|---|
| 0 | 2021-01-02 | 326234700 | 6166.35 |
| 1 | 2021-01-02 | 327260705 | 4073.99 |
| 2 | 2021-01-02 | 3267153714 | 4382.22 |
| 3 | 2021-01-02 | 3295391450 | 9486.65 |
| 4 | 2021-01-02 | 32146147293 | 6078.12 |
'''Теперь нужно для каждого возврата узнать перевозчика, тип доставки и кластер склада'''
returns_for_GMV_D_R = returns_for_GMV_D_R.merge(logistics_online, how = 'left', on = 'number_order')
returns_for_GMV_D_R = returns_for_GMV_D_R[['return_date', 'number_order', 'carrier_name',
'delivery_type', 'warehouse_cluster', 'return_sum']]
returns_for_GMV_D_R.head()
| return_date | number_order | carrier_name | delivery_type | warehouse_cluster | return_sum | |
|---|---|---|---|---|---|---|
| 0 | 2021-01-02 | 326234700 | 3PL 1 | Самовывоз | Кластер Екатеринбург | 6166.35 |
| 1 | 2021-01-02 | 327260705 | 3PL 4 | Самовывоз | Кластер Казань | 4073.99 |
| 2 | 2021-01-02 | 3267153714 | 3PL 1 | Самовывоз | Кластер Москва | 4382.22 |
| 3 | 2021-01-02 | 3267153714 | 3PL 1 | Самовывоз | Кластер Москва | 4382.22 |
| 4 | 2021-01-02 | 3295391450 | Самодоставка | Курьер | Кластер Екатеринбург | 9486.65 |
'''Теперь надо возвраты сгруппировать по нужным признакам'''
returns_for_GMV_D_R = returns_for_GMV_D_R.groupby(['return_date', 'carrier_name', 'delivery_type',
'warehouse_cluster'], as_index = False) \
.agg({'return_sum': 'sum'}) \
.rename(columns = {'return_date': 'date'})
returns_for_GMV_D_R.head()
| date | carrier_name | delivery_type | warehouse_cluster | return_sum | |
|---|---|---|---|---|---|
| 0 | 2021-01-02 | 3PL 1 | Самовывоз | Кластер Екатеринбург | 10033.09 |
| 1 | 2021-01-02 | 3PL 1 | Самовывоз | Кластер Москва | 8764.44 |
| 2 | 2021-01-02 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 7543.78 |
| 3 | 2021-01-02 | 3PL 4 | Самовывоз | Кластер Казань | 4073.99 |
| 4 | 2021-01-02 | Самодоставка | Курьер | Кластер Екатеринбург | 15564.77 |
table_for_GMV_D_R.head()
| date | carrier_name | delivery_type | warehouse_cluster | order_sum | |
|---|---|---|---|---|---|
| 0 | 2021-01-02 | Самодоставка | Курьер | Кластер Москва | 19174.83 |
| 1 | 2021-01-03 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 57859.05 |
| 2 | 2021-01-03 | Самодоставка | Курьер | Кластер Московская область | 49150.79 |
| 3 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 166324.08 |
| 4 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Москва | 30159.34 |
'''Вот итоговый датафрейм в разрезе по всем запрашиваемым признакам'''
GMV_D_R = table_for_GMV_D_R.merge(returns_for_GMV_D_R, how = 'left',
on = ['date', 'carrier_name', 'delivery_type', 'warehouse_cluster']) \
.fillna(0)
GMV_D_R['gmv_dr'] = GMV_D_R['order_sum'] - GMV_D_R['return_sum']
GMV_D_R.head()
| date | carrier_name | delivery_type | warehouse_cluster | order_sum | return_sum | gmv_dr | |
|---|---|---|---|---|---|---|---|
| 0 | 2021-01-02 | Самодоставка | Курьер | Кластер Москва | 19174.83 | 0.0 | 19174.83 |
| 1 | 2021-01-03 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 57859.05 | 0.0 | 57859.05 |
| 2 | 2021-01-03 | Самодоставка | Курьер | Кластер Московская область | 49150.79 | 0.0 | 49150.79 |
| 3 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Екатеринбург | 166324.08 | 0.0 | 166324.08 |
| 4 | 2021-01-04 | 3PL 2 | Самовывоз | Кластер Москва | 30159.34 | 0.0 | 30159.34 |
'''Теперь посмотрим динамику по дням'''
fig = px.line(GMV_D_R, x="date", y="gmv_dr")
fig.show()
'''Динамика по дням в разрезе перевозчиков'''
fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'carrier_name')
fig.show()
'''Динамика по дням в разрезе типов доставки'''
fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'delivery_type')
fig.show()
'''Динамика по дням в разрезе кластеров склада. Лучшие показатели у кластеров Москва и Екатеринбург,
что не удивительно, поскольку это два крупных города, в которых, вероятно, большие заказы'''
fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'warehouse_cluster')
fig.show()